import express from 'express';
import { body, validationResult } from 'express-validator';
import { executeQuery, getOne, transaction } from '../config/database.js';
import { authenticateToken, requireAdmin, requireTeacher } from '../middleware/auth.js';
import { getAttendanceSetting, timeToMinutes } from '../utils/attendanceSettings.js';
import multer from 'multer';

const router = express.Router();

// 配置multer存储
const storage = multer.memoryStorage();
const upload = multer({ storage: storage });

// 导入学生数据
router.post('/import', authenticateToken, requireAdmin, async (req, res) => {
  try {
    const { students } = req.body;
    
    if (!students || !Array.isArray(students) || students.length === 0) {
      return res.status(400).json({
        success: false,
        message: '无效的学生数据'
      });
    }
    
    // 使用事务处理导入
    const result = await transaction(async (conn) => {
      let imported = 0;
      let errors = [];
      
      // 处理每个学生记录
      for (const student of students) {
        try {
          // 验证必要字段
          if (!student.student_no || !student.name || !student.class_id) {
            errors.push({
              student: student.name || student.student_no || '未知',
              error: '缺少必要字段(学号、姓名或班级ID)'
            });
            continue;
          }
          
          // 检查学号是否已存在
          const existingStudent = await getOne(
            'SELECT id FROM students WHERE student_no = ?',
            [student.student_no]
          );
          
          if (existingStudent) {
            // 更新现有学生
            await executeQuery(
              `UPDATE students SET 
                name = ?, 
                class_id = ?, 
                updated_at = CURRENT_TIMESTAMP 
              WHERE id = ?`,
              [
                student.name,
                student.class_id,
                existingStudent.id
              ]
            );
          } else {
            // 生成QR码（使用学号作为QR码）
            const qrCode = student.student_no;
            
            // 插入新学生
            await executeQuery(
              `INSERT INTO students 
                (student_no, name, class_id, qr_code) 
              VALUES (?, ?, ?, ?)`,
              [
                student.student_no,
                student.name,
                student.class_id,
                qrCode
              ]
            );
          }
          
          imported++;
        } catch (studentError) {
          console.error('导入单个学生错误:', studentError);
          errors.push({
            student: student.name || student.student_no || '未知',
            error: studentError.message
          });
        }
      }
      
      return {
        imported,
        errors,
        total: students.length
      };
    });
    
    res.json({
      success: true,
      data: result
    });
    
  } catch (error) {
    console.error('导入学生错误:', error);
    res.status(500).json({
      success: false,
      message: '服务器错误'
    });
  }
});

// 获取学生列表
router.get('/', authenticateToken, async (req, res) => {
  try {
    const { classId, page = 1, limit = 20, search } = req.query;
    const pageNum = parseInt(page) || 1;
    const limitNum = parseInt(limit) || 20;
    const offset = (pageNum - 1) * limitNum;
    const userRole = req.user.role;

    console.log('Students API Debug - Query params:', { classId, search, page, limit });
    console.log('Students API Debug - User role:', userRole);

    let whereClause = '';
    let params = [];

    // 教师只能查看自己负责的班级学生
    if (userRole === 'teacher') {
      const teacherClasses = await executeQuery(`
        SELECT class_id FROM teacher_classes WHERE teacher_id = ?
      `, [req.user.id]);

      if (teacherClasses.length === 0) {
        return res.json({
          success: true,
          data: { students: [], pagination: { page: 1, limit: limitNum, total: 0, pages: 0 } }
        });
      }

      const classIds = teacherClasses.map(tc => tc.class_id);
      whereClause = `WHERE s.class_id IN (${classIds.map(() => '?').join(',')})`;
      params.push(...classIds);

      if (classId && classIds.includes(parseInt(classId))) {
        whereClause = 'WHERE s.class_id = ?';
        params = [parseInt(classId)];
      }
    } else {
      // 管理员可以查看所有学生
      if (classId) {
        whereClause = 'WHERE s.class_id = ?';
        params = [parseInt(classId)];
      }
    }

    // 搜索功能
    if (search) {
      const searchClause = whereClause 
        ? ` AND (s.name LIKE ? OR s.student_no LIKE ?)`
        : ` WHERE (s.name LIKE ? OR s.student_no LIKE ?)`;
      whereClause += searchClause;
      params.push(`%${search}%`, `%${search}%`);
    }

    // 获取学生列表（包含今日考勤状态）
    const today = new Date().toISOString().split('T')[0];
    let sql = `
      SELECT 
        s.id, s.student_no, s.name, s.qr_code, s.created_at,
        c.name as class_name, c.grade,
        -- 今日考勤状态
        ar.arrival_status,
        ar.departure_status,
        TIME(ar.arrival_timestamp) as arrival_time,
        TIME(ar.departure_timestamp) as departure_time,
        ar.notes
      FROM students s
      LEFT JOIN classes c ON s.class_id = c.id
      LEFT JOIN attendance_records ar ON s.id = ar.student_id AND ar.attendance_date = ?
      ${whereClause}
      ORDER BY s.student_no
      LIMIT ${limitNum} OFFSET ${offset}
    `;
    const finalParams = [today, ...params];
    const students = await executeQuery(sql, finalParams);

    // 获取总数
    let countSql = `
      SELECT COUNT(*) as total 
      FROM students s
      LEFT JOIN classes c ON s.class_id = c.id
      ${whereClause}
    `;
    const totalResult = await getOne(countSql, params);

    res.json({
      success: true,
      data: {
        students,
        pagination: {
          page: pageNum,
          limit: limitNum,
          total: totalResult.total,
          pages: Math.ceil(totalResult.total / limitNum)
        }
      }
    });

  } catch (error) {
    console.error('获取学生列表错误:', error);
    res.status(500).json({
      success: false,
      message: '服务器错误'
    });
  }
});

// 获取班级学生（带考勤状态）
router.get('/class/:classId/attendance', authenticateToken, requireTeacher, async (req, res) => {
  try {
    const classId = req.params.classId;
    const { date = new Date().toISOString().split('T')[0] } = req.query;
    const userRole = req.user.role;

    // 教师权限检查
    if (userRole === 'teacher') {
      const hasPermission = await getOne(`
        SELECT 1 FROM teacher_classes WHERE teacher_id = ? AND class_id = ?
      `, [req.user.id, classId]);

      if (!hasPermission) {
        return res.status(403).json({
          success: false,
          message: '无权访问该班级'
        });
      }
    }

    // 获取学生及考勤状态
    const students = await executeQuery(`
      SELECT 
        s.id, s.student_no, s.name, s.qr_code,
        ar.arrival_status, 
        TIME(ar.arrival_timestamp) as arrival_time, 
        ar.departure_status, 
        TIME(ar.departure_timestamp) as departure_time, 
        ar.notes,
        ar.bus_driver_id,
        u.name as driver_name
      FROM students s
      LEFT JOIN attendance_records ar ON s.id = ar.student_id AND ar.attendance_date = ?
      LEFT JOIN drivers d ON ar.bus_driver_id = d.id
      LEFT JOIN users u ON d.user_id = u.id
      WHERE s.class_id = ?
      ORDER BY s.student_no
    `, [date, classId]);

    res.json({
      success: true,
      data: {
        students,
        date,
        class_id: classId
      }
    });

  } catch (error) {
    console.error('获取班级学生考勤错误:', error);
    res.status(500).json({
      success: false,
      message: '服务器错误'
    });
  }
});

// 教师获取自己班级的学生列表（带考勤状态）
router.get('/my-class/attendance', authenticateToken, requireTeacher, async (req, res) => {
  try {
    const { date = new Date().toISOString().split('T')[0] } = req.query;
    const teacherId = req.user.id;

    // 获取教师负责的班级
    const teacherClasses = await executeQuery(`
      SELECT class_id FROM teacher_classes WHERE teacher_id = ?
    `, [teacherId]);

    if (teacherClasses.length === 0) {
      return res.json({
        success: true,
        data: { students: [] }
      });
    }

    const classIds = teacherClasses.map(tc => tc.class_id);

    // 获取所有班级的学生及考勤状态
    const students = await executeQuery(`
      SELECT 
        s.id, s.student_no, s.name, s.qr_code,
        c.id as class_id, c.name as class_name, c.grade,
        ar.id as attendance_id,
        ar.arrival_status, 
        TIME(ar.arrival_timestamp) as arrival_time,
        ar.departure_status, 
        TIME(ar.departure_timestamp) as departure_time,
        ar.bus_driver_id,
        u.name as driver_name,
        ar.notes
      FROM students s
      INNER JOIN classes c ON s.class_id = c.id
      LEFT JOIN attendance_records ar ON s.id = ar.student_id AND ar.attendance_date = ?
      LEFT JOIN drivers d ON ar.bus_driver_id = d.id
      LEFT JOIN users u ON d.user_id = u.id
      WHERE s.class_id IN (${classIds.map(() => '?').join(',')})
      ORDER BY c.grade, c.name, s.student_no
    `, [date, ...classIds]);

    // 检查是否需要自动标记异常（入园时间后未入园的学生）
    const now = new Date();
    const currentDate = now.toISOString().split('T')[0];
    const currentTime = now.getHours() * 60 + now.getMinutes(); // 转换为分钟
    
    // 获取入园时间设置
    const arrivalTimeSetting = await getAttendanceSetting('arrival_time');
    const cutoffTime = timeToMinutes(arrivalTimeSetting); // 动态获取入园时间
    
    // 只有当查询的是今天的数据且当前时间超过入园时间时才执行自动异常逻辑
    if (date === currentDate && currentTime >= cutoffTime) {
      for (const student of students) {
        // 如果学生没有考勤记录、状态为空或状态为absent，自动标记为异常
        if (!student.arrival_status || student.arrival_status === 'absent') {
          try {
            if (student.attendance_id) { 
               // 更新现有记录
               await executeQuery(`
                 UPDATE attendance_records 
                 SET arrival_status = 'abnormal', updated_at = CURRENT_TIMESTAMP
                 WHERE id = ?
               `, [student.attendance_id]);
             } else {
               // 创建新记录
               await executeQuery(`
                 INSERT INTO attendance_records (student_id, attendance_date, arrival_status)
                 VALUES (?, ?, 'abnormal')
               `, [student.id, date]);
             }
             // 更新本地数据以反映变化
             student.arrival_status = 'abnormal';
          } catch (error) {
            console.error(`自动标记学生${student.student_no}异常失败:`, error);
          }
        }
      }
    }

    res.json({
      success: true,
      data: {
        students,
        date
      }
    });

  } catch (error) {
    console.error('获取教师班级学生考勤错误:', error);
    res.status(500).json({
      success: false,
      message: '服务器错误'
    });
  }
});

// 教师获取班级离园考勤信息
router.get('/my-class/departure', authenticateToken, requireTeacher, async (req, res) => {
  try {
    const { date = new Date().toISOString().split('T')[0], page = 1, limit = 50, search } = req.query;
    const teacherId = req.user.id;

    // 获取教师负责的班级
    const teacherClasses = await executeQuery(`
      SELECT class_id FROM teacher_classes WHERE teacher_id = ?
    `, [teacherId]);

    if (teacherClasses.length === 0) {
      return res.json({
        success: true,
        data: { students: [] }
      });
    }

    const classIds = teacherClasses.map(tc => tc.class_id);

    // 构建搜索条件
    let searchCondition = '';
    let searchParams = [];
    if (search) {
      searchCondition = 'AND (s.name LIKE ? OR s.student_no LIKE ?)';
      searchParams = [`%${search}%`, `%${search}%`];
    }

    // 构建IN子句的占位符
    const classPlaceholders = classIds.map(() => '?').join(',');

    // 获取所有班级的学生及离园状态
    const students = await executeQuery(`
      SELECT 
        s.id, s.student_no, s.name, s.qr_code,
        c.id as class_id, c.name as class_name, c.grade,
        ar.id as attendance_id,
        ar.arrival_status, 
        TIME(ar.arrival_timestamp) as arrival_time,
        ar.departure_status,
        ar.departure_method,
        TIME(ar.departure_timestamp) as departure_time,
        ar.departure_marked_time,
        ar.bus_driver_id,
        u.name as driver_name,
        ar.notes,
        -- 计算attendance_status字段
        CASE 
          WHEN ar.arrival_status = 'present' AND ar.departure_status = 'class_entered' THEN 'entered_class'
          WHEN ar.arrival_status = 'present' AND ar.departure_status = 'class_left' THEN 'left_class'
          WHEN ar.arrival_status = 'present' AND ar.departure_status = 'left_school' THEN 'left_school'
          WHEN ar.arrival_status = 'present' THEN 'arrived'
          WHEN ar.arrival_status = 'leave' THEN 'on_leave'
          WHEN ar.arrival_status = 'abnormal' THEN 'abnormal'
          ELSE 'not_arrived'
        END as attendance_status
      FROM students s
      INNER JOIN classes c ON s.class_id = c.id
      LEFT JOIN attendance_records ar ON s.id = ar.student_id AND ar.attendance_date = ?
      LEFT JOIN drivers d ON ar.bus_driver_id = d.id
      LEFT JOIN users u ON d.user_id = u.id
      WHERE s.class_id IN (${classPlaceholders})
      ${searchCondition}
      ORDER BY c.grade, c.name, s.student_no
      LIMIT ? OFFSET ?
    `, [date, ...classIds, ...searchParams, parseInt(limit), (parseInt(page) - 1) * parseInt(limit)]);

    // 获取总数
    const totalResult = await getOne(`
      SELECT COUNT(*) as total
      FROM students s
      INNER JOIN classes c ON s.class_id = c.id
      WHERE s.class_id IN (${classPlaceholders})
      ${searchCondition}
    `, [...classIds, ...searchParams]);

    const total = totalResult.total;

    res.json({
      success: true,
      data: {
        students,
        total,
        page: parseInt(page),
        limit: parseInt(limit),
        totalPages: Math.ceil(total / parseInt(limit))
      }
    });

  } catch (error) {
    console.error('获取教师班级学生离园信息错误:', error);
    res.status(500).json({
      success: false,
      message: '服务器错误'
    });
  }
});

// 教师通过二维码查找自己班级的学生
router.get('/my-class/qr/:code', authenticateToken, requireTeacher, async (req, res) => {
  try {
    const { code } = req.params;
    const teacherId = req.user.id;

    // 获取教师负责的班级
    const teacherClasses = await executeQuery(`
      SELECT class_id FROM teacher_classes WHERE teacher_id = ?
    `, [teacherId]);

    if (teacherClasses.length === 0) {
      return res.status(404).json({
        success: false,
        message: '未找到学生'
      });
    }

    const classIds = teacherClasses.map(tc => tc.class_id);

    // 查找学生（只通过学号验证）
    const student = await getOne(`
      SELECT 
        s.id, s.student_no, s.name, s.qr_code,
        c.id as class_id, c.name as class_name, c.grade
      FROM students s
      INNER JOIN classes c ON s.class_id = c.id
      WHERE s.student_no = ? 
        AND s.class_id IN (${classIds.map(() => '?').join(',')})
    `, [code, ...classIds]);

    if (!student) {
      return res.status(404).json({
        success: false,
        message: '未找到学生或无权访问'
      });
    }

    res.json({
      success: true,
      data: student
    });

  } catch (error) {
    console.error('通过二维码查找学生错误:', error);
    res.status(500).json({
      success: false,
      message: '服务器错误'
    });
  }
});

// 获取单个学生详情
router.get('/:id', authenticateToken, async (req, res) => {
  try {
    const studentId = req.params.id;

    const student = await getOne(`
      SELECT 
        s.id, s.student_no, s.name, s.qr_code, s.created_at,
        c.id as class_id, c.name as class_name, c.grade
      FROM students s
      LEFT JOIN classes c ON s.class_id = c.id
      WHERE s.id = ?
    `, [studentId]);

    if (!student) {
      return res.status(404).json({
        success: false,
        message: '学生不存在'
      });
    }

    // 获取最近的考勤记录
    const recentAttendance = await executeQuery(`
      SELECT 
        attendance_date, 
        arrival_status, 
        TIME(arrival_timestamp) as arrival_time, 
        departure_status, 
        TIME(departure_timestamp) as departure_time, 
        notes
      FROM attendance_records 
      WHERE student_id = ?
      ORDER BY attendance_date DESC
      LIMIT 10
    `, [studentId]);

    res.json({
      success: true,
      data: {
        ...student,
        recent_attendance: recentAttendance
      }
    });

  } catch (error) {
    console.error('获取学生详情错误:', error);
    res.status(500).json({
      success: false,
      message: '服务器错误'
    });
  }
});

// 通过二维码获取学生信息
router.get('/qr/:qrCode', authenticateToken, async (req, res) => {
  try {
    const qrCode = req.params.qrCode;

    const student = await getOne(`
      SELECT 
        s.id, s.student_no, s.name, s.qr_code,
        c.id as class_id, c.name as class_name, c.grade
      FROM students s
      LEFT JOIN classes c ON s.class_id = c.id
      WHERE s.student_no = ?
    `, [qrCode]);

    if (!student) {
      return res.status(404).json({
        success: false,
        message: '未找到学生信息'
      });
    }

    res.json({
      success: true,
      data: student
    });

  } catch (error) {
    console.error('二维码查询学生错误:', error);
    res.status(500).json({
      success: false,
      message: '服务器错误'
    });
  }
});

// 创建学生（仅管理员和教师）
router.post('/', [
  authenticateToken,
  requireTeacher,
  body('student_no').trim().notEmpty().withMessage('学号不能为空'),
  body('name').trim().notEmpty().withMessage('姓名不能为空'),
  body('class_id').isInt().withMessage('班级ID必须是整数'),
  body('assigned_bus_id').optional().isInt().withMessage('校车司机ID必须是整数')
], async (req, res) => {
  try {
    // 验证输入
    const errors = validationResult(req);
    if (!errors.isEmpty()) {
      return res.status(400).json({
        success: false,
        message: '输入验证失败',
        errors: errors.array()
      });
    }

    const { student_no, name, class_id, assigned_bus_id } = req.body;
    const userRole = req.user.role;

    // 教师权限检查
    if (userRole === 'teacher') {
      const hasPermission = await getOne(`
        SELECT 1 FROM teacher_classes WHERE teacher_id = ? AND class_id = ?
      `, [req.user.id, class_id]);

      if (!hasPermission) {
        return res.status(403).json({
          success: false,
          message: '无权在该班级添加学生'
        });
      }
    }

    // 检查学号是否已存在
    const existingStudent = await getOne(
      'SELECT id FROM students WHERE student_no = ?',
      [student_no]
    );

    if (existingStudent) {
      return res.status(400).json({
        success: false,
        message: '学号已存在'
      });
    }

    // 检查班级是否存在
    const classExists = await getOne('SELECT id FROM classes WHERE id = ?', [class_id]);
    if (!classExists) {
      return res.status(400).json({
        success: false,
        message: '班级不存在'
      });
    }

    // 如果提供了校车司机ID，检查司机是否存在
    if (assigned_bus_id) {
      const driverExists = await getOne('SELECT id FROM users WHERE id = ? AND role = "driver"', [assigned_bus_id]);
      if (!driverExists) {
        return res.status(400).json({
          success: false,
          message: '校车司机不存在'
        });
      }
    }

    // 创建学生（不再生成二维码）
    const result = await executeQuery(`
      INSERT INTO students (student_no, name, class_id, assigned_bus_id) 
      VALUES (?, ?, ?, ?)
    `, [student_no, name, class_id, assigned_bus_id || null]);

    res.status(201).json({
      success: true,
      message: '学生添加成功',
      data: {
        id: result.insertId,
        student_no,
        name,
        class_id,
        assigned_bus_id: assigned_bus_id || null
      }
    });

  } catch (error) {
    console.error('创建学生错误:', error);
    res.status(500).json({
      success: false,
      message: '服务器错误'
    });
  }
});

// 更新学生信息
router.put('/:id', [
  authenticateToken,
  requireTeacher,
  body('name').optional().trim().notEmpty().withMessage('姓名不能为空'),
  body('assigned_bus_id').optional().isInt().withMessage('校车司机ID必须是整数')
], async (req, res) => {
  try {
    // 验证输入
    const errors = validationResult(req);
    if (!errors.isEmpty()) {
      return res.status(400).json({
        success: false,
        message: '输入验证失败',
        errors: errors.array()
      });
    }

    const studentId = req.params.id;
    const { name, assigned_bus_id } = req.body;
    const userRole = req.user.role;

    // 检查学生是否存在
    const student = await getOne('SELECT id, class_id FROM students WHERE id = ?', [studentId]);
    if (!student) {
      return res.status(404).json({
        success: false,
        message: '学生不存在'
      });
    }

    // 教师权限检查
    if (userRole === 'teacher') {
      const hasPermission = await getOne(`
        SELECT 1 FROM teacher_classes WHERE teacher_id = ? AND class_id = ?
      `, [req.user.id, student.class_id]);

      if (!hasPermission) {
        return res.status(403).json({
          success: false,
          message: '无权修改该学生信息'
        });
      }
    }

    // 如果提供了校车司机ID，检查司机是否存在
    if (assigned_bus_id !== undefined && assigned_bus_id !== null && assigned_bus_id !== '') {
      const driverExists = await getOne('SELECT id FROM users WHERE id = ? AND role = "driver"', [assigned_bus_id]);
      if (!driverExists) {
        return res.status(400).json({
          success: false,
          message: '校车司机不存在'
        });
      }
    }

    // 构建更新字段
    const updateFields = [];
    const updateValues = [];

    if (name) {
      updateFields.push('name = ?');
      updateValues.push(name);
    }

    if (assigned_bus_id !== undefined) {
      updateFields.push('assigned_bus_id = ?');
      updateValues.push(assigned_bus_id || null);
    }

    if (updateFields.length === 0) {
      return res.status(400).json({
        success: false,
        message: '没有提供要更新的字段'
      });
    }

    updateFields.push('updated_at = CURRENT_TIMESTAMP');
    updateValues.push(studentId);

    await executeQuery(`
      UPDATE students 
      SET ${updateFields.join(', ')} 
      WHERE id = ?
    `, updateValues);

    res.json({
      success: true,
      message: '学生信息更新成功'
    });

  } catch (error) {
    console.error('更新学生错误:', error);
    res.status(500).json({
      success: false,
      message: '服务器错误'
    });
  }
});

// 删除学生
router.delete('/:id', authenticateToken, requireTeacher, async (req, res) => {
  try {
    const studentId = req.params.id;
    const userRole = req.user.role;

    // 检查学生是否存在
    const student = await getOne('SELECT id, class_id FROM students WHERE id = ?', [studentId]);
    if (!student) {
      return res.status(404).json({
        success: false,
        message: '学生不存在'
      });
    }

    // 教师权限检查
    if (userRole === 'teacher') {
      const hasPermission = await getOne(`
        SELECT 1 FROM teacher_classes WHERE teacher_id = ? AND class_id = ?
      `, [req.user.id, student.class_id]);

      if (!hasPermission) {
        return res.status(403).json({
          success: false,
          message: '无权删除该学生'
        });
      }
    }

    await executeQuery('DELETE FROM students WHERE id = ?', [studentId]);

    res.json({
      success: true,
      message: '学生删除成功'
    });

  } catch (error) {
    console.error('删除学生错误:', error);
    res.status(500).json({
      success: false,
      message: '服务器错误'
    });
  }
});

// 管理员更新学生状态接口
router.post('/admin/update-status', [
  authenticateToken,
  requireAdmin,
  body('student_id').isInt().withMessage('学生ID必须是整数'),
  body('arrival_status').optional().isIn(['present', 'leave', 'abnormal']).withMessage('入园状态无效'),
  body('departure_status').optional().isIn(['bus', 'parent_pickup', 'after_school', 'special_course']).withMessage('离园方式无效'),
  body('bus_no').optional().trim(),
  body('notes').optional().trim()
], async (req, res) => {
  try {
    const errors = validationResult(req);
    if (!errors.isEmpty()) {
      return res.status(400).json({
        success: false,
        message: '输入验证失败',
        errors: errors.array()
      });
    }

    const { student_id, arrival_status, departure_status, bus_no, notes } = req.body;
    const today = new Date().toISOString().split('T')[0];
    const now = new Date();
    
    // 如果选择校车离园但未提供校车编号，返回错误
    if (departure_status === 'bus' && !bus_no) {
      return res.status(400).json({
        success: false,
        message: '选择校车离园时必须指定校车编号'
      });
    }

    // 验证学生是否存在
    const student = await getOne(`
      SELECT s.id, s.name, c.name as class_name 
      FROM students s 
      LEFT JOIN classes c ON s.class_id = c.id 
      WHERE s.id = ?
    `, [student_id]);

    if (!student) {
      return res.status(404).json({
        success: false,
        message: '学生不存在'
      });
    }

    // 检查今日是否已有考勤记录
    let attendanceRecord = await getOne(`
      SELECT id FROM attendance_records 
      WHERE student_id = ? AND attendance_date = ?
    `, [student_id, today]);

    if (!attendanceRecord) {
      // 创建新的考勤记录
      const insertSql = `
          INSERT INTO attendance_records 
          (student_id, attendance_date, arrival_status, departure_status, notes, created_at, updated_at)
          VALUES (?, ?, ?, ?, ?, ?, ?)
        `;
      
      let arrivalNotes = arrival_status ? (notes || '管理员修改') : null;
      let departureNotes = departure_status ? (notes || '管理员修改') : null;
      
      // 如果是校车离园，将校车编号添加到备注中
      if (departure_status === 'bus' && bus_no) {
        departureNotes = departureNotes ? `${departureNotes} (校车: ${bus_no})` : `校车: ${bus_no}`;
      }
      
      await executeQuery(insertSql, [
        student_id, today, arrival_status, departure_status, 
        arrivalNotes, departureNotes, now, now
      ]);
    } else {
      // 更新现有考勤记录
      const updateFields = [];
      const updateParams = [];
      
      if (arrival_status) {
        updateFields.push('arrival_status = ?');
        updateParams.push(arrival_status);
        updateFields.push('arrival_timestamp = ?');
        updateParams.push(now);
        updateFields.push('notes = ?');
        updateParams.push(notes || '管理员修改');
      }
      
      if (departure_status) {
        updateFields.push('departure_status = ?');
        updateParams.push(departure_status);
        updateFields.push('departure_timestamp = ?');
        updateParams.push(now);
        updateFields.push('notes = ?');
          updateParams.push(notes || '管理员修改');
        // 如果是校车离园，将校车编号添加到备注中
        if (departure_status === 'bus' && bus_no) {
          departureNotes = departureNotes ? `${departureNotes} (校车: ${bus_no})` : `校车: ${bus_no}`;
        }
        updateParams.push(departureNotes);
      }
      
      if (updateFields.length > 0) {
        updateFields.push('updated_at = ?');
        updateParams.push(now);
        updateParams.push(attendanceRecord.id);
        
        const updateSql = `
          UPDATE attendance_records 
          SET ${updateFields.join(', ')}
          WHERE id = ?
        `;
        
        await executeQuery(updateSql, updateParams);
      }
    }
    
    // 如果是校车离园，添加到校车离园队列
    if (departure_status === 'bus' && bus_no) {
      // 查找指定校车的司机
      const driver = await getOne(`
        SELECT u.id as driver_id, u.bus_no 
        FROM users u 
        WHERE u.role = 'driver' AND u.bus_no = ?
      `, [bus_no]);
      
      if (driver) {
        // 获取或创建今日考勤记录ID
        const currentRecord = await getOne(`
          SELECT id FROM attendance_records 
          WHERE student_id = ? AND attendance_date = ?
        `, [student_id, today]);
        
        if (currentRecord) {
          // 添加到校车离园队列
          await executeQuery(`
            INSERT INTO bus_departure_queue 
            (student_id, attendance_record_id, marked_teacher_id, assigned_driver_id, notes)
            VALUES (?, ?, ?, ?, ?)
            ON DUPLICATE KEY UPDATE 
            assigned_driver_id = VALUES(assigned_driver_id),
            notes = VALUES(notes),
            updated_at = CURRENT_TIMESTAMP
          `, [student_id, currentRecord.id, req.user.id, driver.driver_id, `管理员分配到${bus_no}`]);
        }
      }
    }

    res.json({
      success: true,
      message: `学生 ${student.name} 的状态已更新`
    });

  } catch (error) {
    console.error('更新学生状态错误:', error);
    res.status(500).json({
      success: false,
      message: '服务器错误'
    });
  }
});

// 获取所有学生及考勤状态（支持日期查询）
router.get('/with-attendance', authenticateToken, async (req, res) => {
  try {
    const { date = new Date().toISOString().split('T')[0] } = req.query;
    const userRole = req.user.role;
    
    let whereClause = '';
    let params = [date];
    
    // 教师只能查看自己负责的班级学生
    if (userRole === 'teacher') {
      const teacherClasses = await executeQuery(`
        SELECT class_id FROM teacher_classes WHERE teacher_id = ?
      `, [req.user.id]);
      
      if (teacherClasses.length === 0) {
        return res.json({
          success: true,
          data: []
        });
      }
      
      const classIds = teacherClasses.map(tc => tc.class_id);
      whereClause = `AND s.class_id IN (${classIds.map(() => '?').join(',')})`;
      params.push(...classIds);
    }
    
    // 获取所有学生及考勤状态
    const students = await executeQuery(`
      SELECT 
        s.id, s.student_no, s.name, s.qr_code,
        c.id as class_id, c.name as class_name, c.grade,
        ar.id as attendance_id,
        ar.arrival_status, 
        TIME(ar.arrival_timestamp) as arrival_time,
        ar.departure_status, 
        TIME(ar.departure_timestamp) as departure_time,
        ar.bus_driver_id,
        u.name as driver_name,
        ar.notes
      FROM students s
      INNER JOIN classes c ON s.class_id = c.id
      LEFT JOIN attendance_records ar ON s.id = ar.student_id AND ar.attendance_date = ?
      LEFT JOIN drivers d ON ar.bus_driver_id = d.id
      LEFT JOIN users u ON d.user_id = u.id
      ${whereClause}
      ORDER BY c.grade, c.name, s.student_no
    `, params);
    
    // 检查是否需要自动标记异常（入园时间后未入园的学生）
    const now = new Date();
    const currentDate = now.toISOString().split('T')[0];
    const currentTime = now.getHours() * 60 + now.getMinutes(); // 转换为分钟
    
    // 获取入园时间设置
    const arrivalTimeSetting = await getAttendanceSetting('arrival_time');
    const cutoffTime = timeToMinutes(arrivalTimeSetting); // 动态获取入园时间
    
    // 只有当查询的是今天的数据且当前时间超过入园时间时才执行自动异常逻辑
    if (date === currentDate && currentTime >= cutoffTime) {
      for (const student of students) {
        // 如果学生没有考勤记录或状态为空，自动标记为异常
        if (!student.arrival_status) {
          try {
            if (student.attendance_id) { 
               // 更新现有记录
               await executeQuery(`
                 UPDATE attendance_records 
                 SET arrival_status = 'abnormal', updated_at = CURRENT_TIMESTAMP
                 WHERE id = ?
               `, [student.attendance_id]);
             } else {
               // 创建新记录
               await executeQuery(`
                 INSERT INTO attendance_records (student_id, attendance_date, arrival_status)
                 VALUES (?, ?, 'abnormal')
               `, [student.id, date]);
             }
             // 更新本地数据以反映变化
             student.arrival_status = 'abnormal';
          } catch (error) {
            console.error(`自动标记学生${student.student_no}异常失败:`, error);
          }
        }
      }
    }
    
    res.json({
      success: true,
      data: students
    });
    
  } catch (error) {
    console.error('获取学生及考勤状态错误:', error);
    res.status(500).json({
      success: false,
      message: '服务器错误'
    });
  }
});

export default router;
